package com.bigger.arch.app.repository.dao.impl;

import com.bigger.arch.app.repository.dao.UserDao;
import com.bigger.arch.app.repository.model.UserModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("UserDaoImpl")
public class UserDaoImpl implements UserDao {
    /*
     * 1、Field jdbcTemplate in com.bigger.arch.dao.dao.UserDao required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found.
     * jdbc只设置application.properties还不够，需要JavaConfig注入DataSource，这边采用c3p0连接池。
     * 2、org.springframework.jdbc.CannotGetJdbcConnectionException
     * mysql 8.0以上的版本，需要调整以下配置：
     * spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8&autoReconnect=true
     * spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
     */

    @Autowired
    @Qualifier("jdbc_db_test")
    private JdbcTemplate jdbcTemplate;

    private RowMapper<UserModel> rowMapper;

    public UserDaoImpl() {
        //1、快速匹配；
        //rowMapper = new BeanPropertyRowMapper(UserModel.class);
        //2、自定义匹配
        rowMapper = new RowMapper<UserModel>() {
            public UserModel mapRow(ResultSet resultSet, int i) throws SQLException {
                UserModel userModel = new UserModel();
                if (isExistColumn(resultSet, "id")) {
                    userModel.setId(resultSet.getInt("id"));
                }
                if (isExistColumn(resultSet, "name")) {
                    userModel.setName(resultSet.getString("name"));
                }
                if (isExistColumn(resultSet, "gender")) {
                    userModel.setGender(resultSet.getInt("gender"));
                }
                if (isExistColumn(resultSet, "age")) {
                    userModel.setAge(resultSet.getInt("age"));
                }
                if (isExistColumn(resultSet, "birthday")) {
                    userModel.setBirthday(resultSet.getDate("birthday"));
                }
                if (isExistColumn(resultSet, "address")) {
                    userModel.setAddress(resultSet.getString("address"));
                }
                if (isExistColumn(resultSet, "balance")) {
                    userModel.setBalance(resultSet.getBigDecimal("balance"));
                }
                if (isExistColumn(resultSet, "createTime")) {
                    userModel.setCreateTime(resultSet.getTimestamp("createTime"));
                }
                if (isExistColumn(resultSet, "updateTime")) {
                    userModel.setUpdateTime(resultSet.getTimestamp("updateTime"));
                }
                if (isExistColumn(resultSet, "isDelete")) {
                    userModel.setIsDelete(resultSet.getBoolean("isDelete"));
                }
                return userModel;
            }
        };
    }

    private boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        }

        return false;
    }

    public UserModel getItemById(Integer id) {
        String sql = "SELECT * FROM t_user WHERE ID=?";
        List<UserModel> list = jdbcTemplate.query(sql, rowMapper, new Object[]{id});
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    public UserModel getItemByName(String name) {
        String sql = "SELECT * FROM t_user WHERE Name=?";
        List<UserModel> list = jdbcTemplate.query(sql, rowMapper, new Object[]{name});
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    public List<UserModel> getItems() {
        String sql = "SELECT * FROM t_user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(UserModel.class));
    }

    public List<UserModel> getLimitItems(int offset, int limitSize) {
        return null;
    }

    public List<UserModel> serachItems(UserModel serachModel, int offset, int limitSize) {
        return null;
    }

    public List<UserModel> searchUsersAndArticles(String userName, String title, int offset, int limitSize) {
        return null;
    }

    public BigInteger getCount() {
        String sql = "SELECT count(0) FROM t_user";
        return jdbcTemplate.queryForObject(sql, BigInteger.class);
    }

    public Integer insertItem(final UserModel userModel) {
        if (userModel == null) {
            return 0;
        }

        KeyHolder keyHolder = new GeneratedKeyHolder();
        int result = jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "INSERT INTO t_user (\n" +
                        "  `Name`,\n" +
                        "  `Gender`,\n" +
                        "  `Age`,\n" +
                        "  `Birthday`,\n" +
                        "  `Address`,\n" +
                        "  `Balance`,\n" +
                        "  `CreateTime`,\n" +
                        "  `UpdateTime`,\n" +
                        "  `IsDelete`\n" +
                        ")\n" +
                        "VALUES\n" +
                        "  (\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?,\n" +
                        "    ?\n" +
                        "  );";
                PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                int index = 1;
                preparedStatement.setObject(index++, userModel.getName());
                preparedStatement.setObject(index++, userModel.getGender());
                preparedStatement.setObject(index++, userModel.getAge());
                preparedStatement.setObject(index++, userModel.getBirthday());
                preparedStatement.setObject(index++, userModel.getAddress());
                preparedStatement.setObject(index++, userModel.getBalance());
                preparedStatement.setObject(index++, userModel.getCreateTime());
                preparedStatement.setObject(index++, userModel.getUpdateTime());
                preparedStatement.setObject(index++, userModel.getIsDelete());

                return preparedStatement;
            }
        }, keyHolder);
        if (result <= 0) {
            return result;
        }

        Integer insertID = keyHolder.getKey().intValue();
        userModel.setId(insertID);
        return insertID;
    }

    public Integer updateItem(UserModel userModel) {
        if (userModel == null) {
            return 0;
        }
        String sql = "UPDATE\n" +
                "  t_user \n" +
                "SET\n" +
                "  `Name` = ?,\n" +
                "  `Gender` = ?,\n" +
                "  `Age` = ?,\n" +
                "  `Birthday` = ?,\n" +
                "  `Address` = ?,\n" +
                "  `Balance` = ?,\n" +
                "  `CreateTime` = ?,\n" +
                "  `UpdateTime` = ?,\n" +
                "  `IsDelete` = ?\n" +
                "WHERE `ID` = ?;";
        return jdbcTemplate.update(sql, userModel.getName(), userModel.getGender(), userModel.getAge(), userModel.getBirthday(), userModel.getAddress(), userModel.getBalance(), userModel.getCreateTime(), userModel.getUpdateTime(), userModel.getIsDelete(), userModel.getId());
    }

    @Autowired
    @Qualifier("named_jdbc_db_test")
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public Integer updateBalance(UserModel userModel) {
        if (userModel == null) {
            return 0;
        }
        String sql = "UPDATE t_user SET Balance=:Balance WHERE ID=:ID";
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("Balance", userModel.getBalance());
        parameters.put("ID", userModel.getId());
        return namedParameterJdbcTemplate.update(sql, parameters);
    }

    public Integer deleteItem(UserModel userModel) {
        return null;
    }

    public Integer deleteItems(List<Integer> ids) {
        return null;
    }
}
